'use strict';
const HTM = require('./utils').html;
const dateToString = require('./utils').dateToString;
const stringToDate = require('./utils').stringToDate;
const addDate = require('./utils').addDate;
const db = require("../coSqlite3");

exports.AddReader = function*(req) {
    // TODO 添加读者
    let body = req.body;
    //rID不为空
    if(body.rID){
        let chkID = yield db.execSQL("select * from reader where readerID =  ? ", [body.rID]);
        //账号已存在
        if(chkID[0]){
            return HTM.begin + "账号已存在" + HTM.end;
        } 
        //账号合理
        else {
            
            if(Math.round(body.rGrade) == body.rGrade && body.rName.length<11 && body.rDept.length<11){
                if(body.rSex == '男' || body.rSex == '女'){
                    yield db.execSQL("insert into reader(readerID,readerName,readerSex,readerDept,readerGrade) values(?,?,?,?,?)", [body.rID,body.rName,body.rSex,body.rDept,body.rGrade]);
                }
                else{
                    yield db.execSQL("insert into reader(readerID,readerName,readerSex,readerDept,readerGrade) values(?,?,?,?,?)", [body.rID,body.rName,'男',body.rDept,body.rGrade]);
                }
                return HTM.begin + "<div id='result' style='display:none'>0</div>" + "成功" + HTM.end;
            } else{
                return HTM.begin + "<div id='result' style='display:none'>2</div>" + "提交参数有误" + HTM.end;
            }
        }
    } else{
        return HTM.begin + "<div id='result' style='display:none'>2</div>" + "提交参数有误" + HTM.end;
    }
};


exports.DelReader = function*(req) {
    // TODO 删除读者
    let body = req.body;
    let chkDelID = yield db.execSQL("select * from reader where readerID = ?",[body.rID] );
    if(body.rID == '' ||chkDelID[0] == ''){
        console.log(1);
        return HTM.begin + "该证号不存在" + HTM.end;
    }else{
        yield db.execSQL("delete from reader where readerID = ? ",[body.rID]);
        console.log(0);
        return HTM.begin + "<div id='result' style='display:none'>0</div>" + "成功" + HTM.end;
    }

    //该读者尚有书籍未归还
    
};


exports.ChangeReaderInfo = function*(req) {
    // TODO 修改读者信息
    let body = req.body;
    if(body.rID){
        //格式合法检测
        if(Math.round(body.rGrade) == body.rGrade && body.rName.length<11 && body.rDept.length<11){
            //修改
            


            //yield db.execSQL("update reader set (readerID,readerName,readerSex,readerDept,readerGrade) values(?,?,?,?,?) where readerID = ?", [body.rID,body.rName,body.rSex,body.rDept,body.rGrade,body.rID]);
        }
        else{
            return HTM.begin + "提交的参数有误" + HTM.end;
        }
    }else{
        return HTM.begin + "该证号不存在" + HTM.end;
    }

};


exports.QueryReader = function*(req) {
    // TODO 查询读者
    let body = req.body;
    if(body.rID || body.rName || body.rSex || body.rDept || body.rGrade || body.rID){
        //模糊查询
    
    } 
    //返回全部信息
    else {
        let sqlALL = "SELECT * FROM reader";
        let rstSLC = yield db.exceSQL (sqlALL);
        //return HTM.begin + "<table border=1 id='result'>"+HTM.end;

        for ( i = 0; i< rstSLC.length ; i++){
            return rstSLC[0]
            //return HTM.begin + + HTM.end;
            /*
            <html><head><META HTTP-EQUIV="Content-Type" Content="text-html;charset=utf-8"></head>
            <body>
            <table border=1 id='result'>
            <tr><td>B-301</td><td>张三</td><td>男</td><td>计算机</td><td>2005</td></tr>
            <tr><td>90-45</td><td>李四</td><td>女</td><td>中文</td><td>2006</td></tr>
            <tr><td>…</td><td>…</td><td>男</td><td>…</td><td>…</td></tr>
            </table>
            </body>
            </html>
            */

        }


    }


};


exports.QueryUnreturnedBook = function*(req) {
    // 查询未归还的书籍
    let body = req.body;
    let IDchk = yield db.execSQL("SELECT * FROM reader WHERE readerID = ?", [body.rID]);
    if(IDchk){
        // 读者ID存在

        // 未还书列表
        let books = [];
        // 是否有超期书未还
        let is_exceed = false;

        let RCDchk = yield db.execSQL("SELECT * FROM record WHERE recordReaderID = ? ", [body.rID]);
        if (RCDchk.length == 0){
            return HTM.begin + "<table border=1 id='result'></table>" + HTM.end;
        } else {
            let htm_str = HTM.begin + "<table border=1 id='result'>";
            for (let i = 0; i < RCDchk.length; i++){
                let book_id = RCDchk[i].recordBookID;
                books.push(book_id);
                let book_info = yield db.execSQL("SELECT bookName, borrowTime, oughtReturnTime FROM book, record WHERE bookID = ? and recordReaderID = ? and returnTime is NULL", [book_id, body.rID]);
                htm_str += "<tr><td>" + book_id + "</td><td>" + book_info[i]['bookName'] + 
                           "</td><td>" + book_info[i]['borrowTime'] + "</td><td>" + book_info[i]['oughtReturnTime'];
                if (new Date() > stringToDate(book_info[i]['oughtReturnTime'])){
                    htm_str += "</td><td>是</td></tr>";
                    is_exceed = true;
                } else {
                    htm_str += "</td><td>否</td></tr>";
                }
            }
            htm_str += "</table>" + HTM.end;
            return htm_str;
        }
    } else {
        // 读者ID不存在
        return HTM.begin + "<div id='result' style='display:none'>1</div>" + "该证号不存在" + HTM.end;
    }     
    
};


exports.BorrowBook = function*(req) {
    // 借书
    // 每次借书的时候新建一个record，需要一个读者id、书籍id以及借书时间和归还时间；

    // 最大还书时间限制
    const LIMIT = 30;

    let body = req.body;
    const base_err = ['该证号不存在', '该书号不存在', '该读者有超期书未还', '该读者已经借阅该书，且未归还', '该书已经全部借出', 'sql语句执行错误']

    // 检查读者是否存在
    let chkReader = yield db.execSQL("SELECT * FROM reader WHERE readerID = ?", [body.rID]);
    if (chkReader.length == 0) {
        return HTM.begin + "<div id='result' style='display:none'>1</div>" + base_err[0] + HTM.end;
    } else {
        // 检查书籍是否存在
        let chkBook = yield db.execSQL("SELECT * FROM book WHERE bookID = ?", [body.bID]);
        if (chkBook.length == 0) {
            return HTM.begin + "<div id='result' style='display:none'>2</div>" + base_err[1] + HTM.end;
        } else {
            // 读者的未还书列表
            let book_info = yield db.execSQL("SELECT * FROM record WHERE recordReaderID = ? and returnTime is NULL", [body.rID]);
            let books = [];
            let is_exceed = false;
            for (let i = 0; i < book_info.length; i++) {
                if (new Date() > stringToDate(book_info[i]['oughtReturnTime'])) {
                    is_exceed = true;
                }
                books.push(book_info[i]['recordBookID']);
            }
            
            // 是否有超期书未还
            if (is_exceed) {
                return HTM.begin + "<div id='result' style='display:none'>3</div>" + base_err[2] + HTM.end;
            } else {
                // 该读者已经借阅该书，且未归还
                if (books.indexOf(body.bID) != -1) {
                    return HTM.begin + "<div id='result' style='display:none'>4</div>" + base_err[3] + HTM.end;
                } else {
                    let chkBookCnt = yield db.execSQL("SELECT bookCnt FROM book WHERE bookID = ?", [body.bID]);
                    if (chkBookCnt[0]['bookCnt'] == 0) {
                        // 该书已经全部借出
                        return HTM.begin + "<div id='result' style='display:none'>5</div>" + base_err[4] + HTM.end;
                    } else {
                        // 借书正常流程
                        let record_id = yield db.execSQL("SELECT Count(recordID) FROM record");
                        record_id = record_id[0]['Count(recordID)'] + 1;
                        try {
                            let now_date = new Date();
                            yield db.execSQL("INSERT INTO record (recordID, recordReaderID, recordBookID, borrowTime, returnTime, oughtReturnTime) VALUES (?, ?, ?, ?, ?, ?)", 
                                            [record_id, body.rID, body.bID, dateToString(now_date), null, dateToString(addDate(now_date, LIMIT, 0, 0))]);
                            // 更新book表中的bookCnt
                            yield db.execSQL("UPDATE book SET bookCnt = bookCnt - 1 WHERE bookID = ?", [body.bID]);
                            return HTM.begin + "<div id='result' style='display:none'>0</div>" + "借书成功" + HTM.end;
                        } catch {
                            // sql语句执行出错
                            return HTM.begin + "<div id='result' style='display:none'>6</div>" + base_err[5] + HTM.end;
                        }
                    }
                }
            }
        }
    }
    
};


exports.ReturnBook = function*(req) {
    // 还书
    // 还书时更新record的还书时间，是否超期 可通过当前时间和应还书的时间对比得到。如果没有还书，还书时间是null
    
    let body = req.body;
    const base_err = ['该证号不存在', '该书号不存在', '该读者未借阅该书', 'sql语句执行错误'];

    // 检查读者是否存在
    let chkReader = yield db.execSQL("SELECT * FROM reader WHERE readerID = ?", [body.rID]);
    if (chkReader.length == 0) {
        return HTM.begin + "<div id='result' style='display:none'>1</div>" + base_err[0] + HTM.end;
    } else {
        // 检查书籍是否存在
        let chkBook = yield db.execSQL("SELECT * FROM book WHERE bookID = ?", [body.bID]);
        if (chkBook.length == 0) {
            return HTM.begin + "<div id='result' style='display:none'>2</div>" + base_err[1] + HTM.end;
        } else {
            // 读者的未还书列表
            let book_info = yield db.execSQL("SELECT * FROM record WHERE recordReaderID = ? and recordBookID = ? and returnTime is NULL", [body.rID, body.bID]);
            // 检查读者是否借阅该书
            if (book_info.length == 0) {
                return HTM.begin + "<div id='result' style='display:none'>3</div>" + base_err[2] + HTM.end;
            } else {
                // 还书正常流程
                try {
                    let now_date = new Date();
                    yield db.execSQL("UPDATE record SET returnTime = ? WHERE recordReaderID = ? and recordBookID = ? and returnTime is NULL", 
                                     [dateToString(now_date), body.rID, body.bID]);
                    // 更新book表中的bookCnt
                    yield db.execSQL("UPDATE book SET bookCnt = bookCnt + 1 WHERE bookID = ?", [body.bID]);
                    return HTM.begin + "<div id='result' style='display:none'>0</div>" + "还书成功" + HTM.end;
                } catch {
                    // sql语句执行出错
                    return HTM.begin + "<div id='result' style='display:none'>4</div>" + base_err[3] + HTM.end;
                }
            }
        }
    }
};


exports.QueryExceedReaders = function*(req) {
    // TODO 查看超期读者
    
};